﻿using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using Enterprise.Utility;
using System.Collections.Generic;

namespace Enterprise.Dal
{
    /// <summary>
    /// SqlHelper 的摘要说明
    /// </summary>
    public sealed class SqlHelper
    {
        private static string ReturnConnectionStr()
        {
            string ConnStr = ConfigurationManager.AppSettings["myconn"].ToString();
            if (ConfigurationManager.AppSettings["IsEncrypt"] != null && Convert.ToBoolean(ConfigurationManager.AppSettings["IsEncrypt"].ToString()))
            {
                if (ConfigurationManager.AppSettings["WebName"] != null)
                {
                    string EncryptKey = "kms€" + ConfigurationManager.AppSettings["WebName"].ToString();
                    ConnStr = SecurityUtils.DesDecrypt(ConnStr, EncryptKey);
                }
            }
            return ConnStr;
        }
        //public static string connectionString = ConfigurationSettings.AppSettings["myconn"].ToString();
        public static string connectionString = ReturnConnectionStr();

        #region 私有构造函数和方法

        /// <summary>
        /// 私有构造函数，是单件模式，防止被其他类通过“new SqlHelper()”实例化
        /// </summary>
        private SqlHelper()
        {
            //空构造函数
        }

        /// <summary>
        /// 参数处理，使之附加到command对象
        /// </summary>
        /// <param name="command">SqlCommand对象</param>
        /// <param name="commandParameters">参数集</param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            //command不能为空异常
            if (command == null) throw new ArgumentNullException("command");

            //处理commandParameters，使之加入到SqlCommand
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // 用字段的有效值“空对象[DBNull.Value]”来代替“空[null]”
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        /// <summary>
        /// 分配dataRow列值到SqlParameters数组
        /// </summary>
        /// <param name="commandParameters">SqlParameters数组</param>
        /// <param name="dataRow">数据行</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null))
            {
                return;
            }

            int i = 0;

            // 设置参数值
            foreach (SqlParameter commandParameter in commandParameters)
            {
                // 检查参数名
                if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1)
                {
                    throw new Exception(string.Format("请提供一个合法的参数名到参数 #{0}, 参数名属性的值如下：{1}.", i, commandParameter.ParameterName));
                }
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                {
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                }

                i++;
            }
        }

        /// <summary>
        /// 分配一个对象数组值到SqlParameters数组
        /// </summary>
        /// <param name="commandParameters">SqlParameters数组</param>
        /// <param name="parameterValues">对象数组值</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }

            // 两数组的大小必须一致
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("对象数组和SqlParameters数组大小要一致！");
            }

            //执行分配
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // 如果当前值由IDbDataParameter派生，则进行值分配
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }

        /// <summary>
        /// 参数数组预备，如果需要，则打开和分配一个数据连接、事务、命令类型、参数到Command对象。
        /// </summary>
        /// <param name="command">Command对象</param>
        /// <param name="connection">执行Command对象的数据连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">T-SQL语句</param>
        /// <param name="commandParameters">Command参数</param>
        /// <param name="mustCloseConnection">是否必须关闭连接</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            //command不能为空异常
            if (command == null) throw new ArgumentNullException("command");

            //commandText不能为空异常
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // 如果数据连接没打开，则打开
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // 分配数据连接
            command.Connection = connection;

            // 设置T-SQL
            command.CommandText = commandText;

            // 如果存在事务则分配
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("事务将被回滚或执行，请提供一个存在的事务", "transaction");
                command.Transaction = transaction;
            }

            // 设置命令类型
            command.CommandType = commandType;

            // 附加参数
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        #endregion

        #region 执行一条没有返回数据结果集的 SqlCommand [ExecuteNonQuery]

        /// <summary>
        /// 执行一条没有返回数据结果集的 SqlCommand 
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接串</param>
        /// <param name="commandType">SQL命令类型：SQL语句或存储过程</param>
        /// <param name="commandText">命令字符串</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条没有返回数据结果集的 SqlCommand 
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接串</param>
        /// <param name="commandType">TSQL命令类型：SQL语句或存储过程</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParameters">执行需要的参数数组</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //连接字符串不能为空
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("连接字符串不能为空");

            //在 using 范围外自动销毁 connection 对象
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                //执行
                return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// 执行一条没有返回结果的存储过程
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(connString, "PublishOrders", parameterValues);
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">执行需要的参数</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条没有返回结果的 SqlCommand
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条没有返回结果的 SqlCommand
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            int retval = cmd.ExecuteNonQuery();

            // 销毁对象
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

        /// <summary>
        /// 执行一条没有返回结果的存储过程
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条没有返回结果的 SqlCommand
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条没有返回结果的 SqlCommand
        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            int retval = cmd.ExecuteNonQuery();

            // 销毁对象
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary>
        /// 执行一条没有返回结果的 SqlCommand
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region 执行一条 SqlCommand 返回 DataSet 数据集[ExecuteDataset]

        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            // 创建数据连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 填充数据集
                return ExecuteDataset(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// 执行一条存储过程返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集 
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建适配器和数据集
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // 填充数据集
                da.Fill(ds);

                // 销毁对象
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                // 返回
                return ds;
            }
        }

        /// <summary>
        /// 执行一条存储过程返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建适配器和数据集
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // 填充数据集
                da.Fill(ds);

                // 销毁对象
                cmd.Parameters.Clear();

                // 返回数据集
                return ds;
            }
        }

        /// <summary>
        /// 执行一条存储过程返回 DataSet 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteDataset

        #region 执行一条 SqlCommand 返回 SqlDataReader对象[ExecuteReader]

        /// <summary>
        /// 用于声明数据库连接是“外部调用”、“SqlHelper创建”、的枚举型
        /// </summary>
        private enum SqlConnectionOwnership
        {
            /// <summary>SqlHelper创建</summary>
            Internal,

            /// <summary>外部调用</summary>
            External
        }

        /// <summary>
        /// 创建 SqlCommand
        /// </summary>
        /// <param name="connection">有效的数据库连接对象, on which to execute this command</param>
        /// <param name="transaction">有效的存储过程对象, or 'null'</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <param name="connectionOwnership">数据库连接身份</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                SqlDataReader dataReader;

                if (connectionOwnership == SqlConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                bool canClear = true;
                foreach (SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

                return dataReader;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }

        /// <summary>
        /// 创建 SqlCommand
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="transaction">有效的存储过程对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <param name="commandBehavior">提供对查询结果和查询对数据库的影响的说明</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, CommandBehavior commandBehavior)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            try
            {
                PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                SqlDataReader dataReader;

                dataReader = cmd.ExecuteReader(commandBehavior);

                bool canClear = true;
                foreach (SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    cmd.Parameters.Clear();
                }

                return dataReader;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", CommandBehavior.SequentialAccess);
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandBehavior">提供对查询结果和查询对数据库的影响的说明</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, CommandBehavior commandBehavior)
        {
            return ExecuteReader(connectionString, commandType, commandText, commandBehavior, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();

                return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
            }
            catch
            {
                if (connection != null) connection.Close();
                throw;
            }

        }
        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", CommandBehavior.SequentialAccess, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandBehavior">提供对查询结果和查询对数据库的影响的说明</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, CommandBehavior commandBehavior, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();

                return ExecuteReader(connection, null, commandType, commandText, commandParameters, commandBehavior);
            }
            catch
            {
                if (connection != null) connection.Close();
                throw;
            }

        }
        /// <summary>
        /// 执行一条存储过程返回 SqlDataReader 对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        }

        /// <summary>
        /// 执行一条存储过程返回 SqlDataReader 对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条存储过程返回 SqlDataReader 对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 SqlDataReader对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
        }

        /// <summary>
        /// 执行一条存储过程返回 SqlDataReader 对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 SqlDataReader 对象</returns>
        public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                AssignParameterValues(commandParameters, parameterValues);

                return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteReader

        #region 执行一条 SqlCommand 返回单元格数据集[ExecuteScalar]

        /// <summary>
        /// 执行一条 SqlCommand 返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            // 创建数据连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 填充数据集
                return ExecuteScalar(connection, commandType, commandText, commandParameters);
            }
        }

        /// <summary>
        /// 执行一条存储过程返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            object retval = cmd.ExecuteScalar();

            // 销毁对象
            cmd.Parameters.Clear();

            if (mustCloseConnection)
            {
                connection.Close();
            }
            return retval;
        }

        /// <summary>
        /// 执行一条存储过程返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            object retval = cmd.ExecuteScalar();

            // 销毁对象
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary>
        /// 执行一条存储过程返回单元格数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // P从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteScalar

        #region 执行一条 SqlCommand 返回 XmlReader数据集[ExecuteXmlReader]
        /// <summary>
        /// 执行一条 SqlCommand 返回 XmlReader数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 XmlReader数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;
            try
            {
                PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // 执行
                XmlReader retval = cmd.ExecuteXmlReader();

                // 销毁对象
                cmd.Parameters.Clear();

                return retval;
            }
            catch
            {
                if (mustCloseConnection)
                {
                    connection.Close();
                }
                throw;
            }
        }

        /// <summary>
        /// 执行一条存储过程返回 XmlReader 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称 using "FOR XML AUTO"</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 XmlReader数据集 
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
        }

        /// <summary>
        /// 执行一条 SqlCommand 返回 XmlReader数据集 
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 600;

            bool mustCloseConnection = false;

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建适配器和数据集
            XmlReader retval = cmd.ExecuteXmlReader();

            // 销毁对象
            cmd.Parameters.Clear();
            return retval;
        }

        /// <summary>
        /// 执行一条存储过程返回 XmlReader 数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        /// <returns>返回 XmlReader 数据集</returns>
        public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 填充数据集
                return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteXmlReader

        #region 填充数据集[FillDataset]

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // 创建数据连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 填充数据集
                FillDataset(connection, commandType, commandText, dataSet, tableNames);
            }
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">参数数组</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // 创建数据连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 填充数据集
                FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
            }
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>    
        /// <param name="parameterValues">存储过程输入值数组</param>
        public static void FillDataset(string connectionString, string spName, DataSet dataSet, string[] tableNames, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // 创建数据连接
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 填充数据集
                FillDataset(connection, spName, dataSet, tableNames, parameterValues);
            }
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>    
        public static void FillDataset(SqlConnection connection, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        /// <param name="commandParameters">参数数组</param>
        public static void FillDataset(SqlConnection connection, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params SqlParameter[] commandParameters)
        {
            FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        public static void FillDataset(SqlConnection connection, string spName, DataSet dataSet, string[] tableNames, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (dataSet == null) throw new ArgumentNullException("dataSet");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // 填充数据集
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        public static void FillDataset(SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        /// <param name="commandParameters">参数数组</param>
        public static void FillDataset(SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params SqlParameter[] commandParameters)
        {
            FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
        /// </remarks>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        /// <param name="parameterValues">存储过程输入值数组</param>
        public static void FillDataset(SqlTransaction transaction, string spName, DataSet dataSet, string[] tableNames, params object[] parameterValues)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (dataSet == null) throw new ArgumentNullException("dataSet");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果接收到参数值，则必须初始化
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 分配值到参数数组
                AssignParameterValues(commandParameters, parameterValues);

                // 填充数据集
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // 填充数据集
                FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="commandType">命令类型[存储过程或Text]</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="dataSet">数据集</param>
        /// <param name="tableNames">数据集中的表名</param>
        /// <param name="commandParameters">参数数组</param>
        private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // 创建 SqlCommand 对象
            SqlCommand command = new SqlCommand();

            bool mustCloseConnection = false;

            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建适配器和数据集
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            {

                // 添加表映射
                if (tableNames != null && tableNames.Length > 0)
                {
                    string tableName = "Table";
                    for (int index = 0; index < tableNames.Length; index++)
                    {
                        if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("必须是个表名数组", "tableNames");

                        dataAdapter.TableMappings.Add(tableName, tableNames[index]);

                        tableName += (index + 1).ToString();
                    }
                }

                // 填充数据集
                dataAdapter.Fill(dataSet);

                // 销毁对象
                command.Parameters.Clear();
            }

            if (mustCloseConnection)
            {
                connection.Close();
            }
        }
        #endregion

        #region 更新数据集[UpdateDataSet]
        /// <summary>
        /// 更新数据集
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        /// </remarks>
        /// <param name="insertCommand">新增Command </param>
        /// <param name="deleteCommand">删除Command</param>
        /// <param name="updateCommand">更新Command</param>
        /// <param name="dataSet">被更新的数据集</param>
        /// <param name="tableName">被更新的数据集的表名</param>
        public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");

            if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");

            if (updateCommand == null) throw new ArgumentNullException("updateCommand");

            if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // 创建一个适配器来执行Command
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
            {
                // 设置Command
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // 更新数据集
                dataAdapter.Update(dataSet, tableName);

                // 执行
                dataSet.AcceptChanges();
            }
        }
        #endregion

        #region 创建 SqlCommand 对象[CreateCommand]
        /// <summary>
        /// 创建 SqlCommand 对象
        /// </summary>
        /// <remarks>
        /// 例如：  
        /// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
        /// </remarks>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="sourceColumns">被分配作为存储过程参数源列的字符数组</param>
        /// <returns>有效的 SqlCommand 对象</returns>
        public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 创建 SqlCommand 对象
            SqlCommand cmd = new SqlCommand(spName, connection);

            cmd.CommandType = CommandType.StoredProcedure;

            // 如果接收到参数值，则需要进行计算处理
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 分配源列成参数数组
                for (int index = 0; index < sourceColumns.Length; index++)
                    commandParameters[index].SourceColumn = sourceColumns[index];

                // 附加参数到 SqlCommand对象
                AttachParameters(cmd, commandParameters);
            }

            return cmd;
        }
        #endregion

        #region 执行一条带类型化参数的，但没有返回值的存储过程[ExecuteNonQueryTypedParams]
        /// <summary>
        /// 执行一条带类型化参数的，但没有返回值的存储过程
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数的，但没有返回值的存储过程
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connection">有效的数据连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数的，但没有返回值的存储过程
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region 执行一条带类型化参数的存储过程，返回一个 DataSet 数据集[ExecuteDatasetTypedParams]
        /// <summary>
        /// 执行一条带类型化参数的存储过程，返回一个 DataSet 数据集
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数的存储过程，返回一个 DataSet 数据集
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数的存储过程，返回一个 DataSet 数据集
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 DataSet 数据集</returns>
        public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
            }
        }

        #endregion

        #region 执行一条带类型化参数存储过程，返回一个 SqlDataReader 结果集[ExecuteReaderTypedParams]

        /// <summary>
        /// 执行一条带类型化参数存储过程，返回一个 SqlDataReader 结果集
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 SqlDataReader 结果集</returns>
        public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
            }
        }


        /// <summary>
        /// 执行一条带类型化参数存储过程，返回一个 SqlDataReader 结果集
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 SqlDataReader 结果集</returns>
        public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数存储过程，返回一个 SqlDataReader 结果集
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="transaction">有效事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 SqlDataReader 结果集</returns>
        public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region 执行一条带类型化参数存储过程，返回一个单元格数据集[ExecuteScalarTypedParams]

        /// <summary>
        /// 执行一条带类型化参数存储过程，返回一个单元格 
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数存储过程，返回一个单元格 
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数存储过程，返回一个单元格 
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回单元格数据集</returns>
        public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        #region 执行一条带类型化参数的存储过程，返回 XmlReader 结果集[ExecuteXmlReaderTypedParams]

        /// <summary>
        /// 执行一条带类型化参数的存储过程，返回XmlReader结果集 
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="connection">有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 XmlReader 结果集</returns>
        public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行一条带类型化参数的存储过程，返回XmlReader结果集 
        /// 使用 dataRow 列值作为存储过程的参数值
        /// 这个方法查询数据库，对存储过程的参数进行发现和分配
        /// </summary>
        /// <param name="transaction">有效的事务对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataRow">数据行</param>
        /// <returns>返回 XmlReader 结果集</returns>
        public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");

            if (transaction != null && transaction.Connection == null) throw new ArgumentException("事务将被回滚或提交，请提供一个存在的事务对象", "transaction");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            // 如果行有值，存储过程参数数组必须被初始化
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // 从参数缓存中初始化存储过程参数数组
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

                // 设置存储过程参数数组值
                AssignParameterValues(commandParameters, dataRow);

                return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
            }
        }
        #endregion

        //----------------add by jasonli 增加多条SQL语句的事务处理---------------------
        #region 执行多条SQL语句，实现数据库事务
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        #endregion
        //--------------------------------------------------------------------------
        #region 执行存储过程
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string[] parName, out object[] returnValue)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                returnValue = new object[parName.Length];
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                for (int i = 0; i < parName.Length; i++)
                {
                    returnValue[i] = sqlDA.SelectCommand.Parameters[parName[i]].Value;
                }
                connection.Close();
                return dataSet;
            }
        }
        #endregion
        #region 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }
        #endregion
    }

    /// <summary>
    /// SqlHelperParameterCache类提供参数发现和缓存功能，也是一个密封类，防止被继承。
    /// </summary>
    public sealed class SqlHelperParameterCache
    {

        #region 私有构造函数、变量、方法

        /// <summary>
        /// 私有构造函数，防止被实例化
        /// </summary>
        private SqlHelperParameterCache()
        {
            //空构造函数
        }

        /// <summary>
        /// 参数缓存哈希变量
        /// </summary>
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 在运行时分析存储过程正确的参数集
        /// </summary>
        /// <param name="connection">有效的数据库连接象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="includeReturnValueParameter">是否包含返回参数</param>
        /// <returns>被发现的参数数组</returns>
        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            SqlCommand cmd = new SqlCommand(spName, connection);

            cmd.CommandType = CommandType.StoredProcedure;

            connection.Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();

            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(discoveredParameters, 0);

            //用空对象[DBNull.Value]初始化参数集 
            foreach (SqlParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }

            return discoveredParameters;
        }

        /// <summary>
        /// 克隆参数数组
        /// </summary>
        /// <param name="originalParameters">源参数数组</param>
        /// <returns>处理后的参数数组</returns>
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        #endregion

        #region 缓存函数

        /// <summary>
        /// 将参数数组存储到缓存中
        /// </summary>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <param name="commandParameters">将被缓存的参数数组</param>
        public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        /// <summary>
        /// 从缓存中恢复参数数组
        /// </summary>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="commandText">命令字符串[存储过程名称或T-SQL命令]</param>
        /// <returns>返回被缓存的参数数组</returns>
        public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];

            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }

        #endregion

        #region 参数发现函数

        /// <summary>
        /// 为存储过程恢复有效的参数集
        /// </summary>
        /// <remarks>
        /// 这个方法将执行数据库查询的相关参数集存储到缓存中，以便今后再次调用
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <returns>返回执行存储过程所需的参数数组</returns>
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        /// <summary>
        /// 为存储过程恢复有效的参数集
        /// </summary>
        /// <remarks>
        /// 这个方法将执行数据库查询的相关参数集存储到缓存中，以便今后再次调用
        /// </remarks>
        /// <param name="connectionString">有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="includeReturnValueParameter">是否包含返回参数</param>
        /// <returns>返回执行存储过程所需的参数数组</returns>
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// 为存储过程恢复有效的参数集
        /// </summary>
        /// <remarks>
        /// 这个方法将执行数据库查询的相关参数集存储到缓存中，以便今后再次调用
        /// </remarks>
        /// <param name="connection">有效的数据连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <returns>返回执行存储过程所需的参数数组</returns>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        /// <summary>
        /// 为存储过程恢复有效的参数集
        /// </summary>
        /// <remarks>
        /// 这个方法将执行数据库查询的相关参数集存储到缓存中，以便今后再次调用
        /// </remarks>
        /// <param name="connection">有效的数据连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="includeReturnValueParameter">是否包含返回参数</param>
        /// <returns>返回执行存储过程所需的参数数组</returns>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// 为存储过程恢复有效的参数集
        /// </summary>
        /// <param name="connection">有效的数据连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="includeReturnValueParameter">是否包含返回参数</param>
        /// <returns>返回执行存储过程所需的参数数组</returns>
        private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            SqlParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as SqlParameter[];

            if (cachedParameters == null)
            {
                SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);
        }

        #endregion

    }

    /// <summary>
    /// SQLStrBuilder类提供构造SQL语句功能，也是一个密封类，防止被继承。
    /// </summary>
    public sealed class SqlStringBuilder
    {

        #region 私有构造函数，防止被实例化
        /// <summary>
        /// 私有构造函数，防止被实例化
        /// </summary>
        private SqlStringBuilder()
        {
            //空构造函数
        }
        #endregion

        #region SQL语句过滤
        /// <summary>
        /// SQL语句过滤
        /// </summary>
        /// <param name="strSQL">原始SQL语句</param>
        /// <returns>过滤后的SQL语句</returns>
        public static string SQLFilter(string strSQL)
        {
            //单引号替换成两个单引号
            strSQL = strSQL.Replace("'", "''");

            //半角封号替换为全角封号
            strSQL = strSQL.Replace(";", "；");

            //半角括号替换为全角括号
            strSQL = strSQL.Replace("(", "（");
            strSQL = strSQL.Replace(")", "）");

            //去除执行存储过程的命令关键字
            strSQL = strSQL.Replace("Exec", "");
            strSQL = strSQL.Replace("Execute", "");

            //去除系统存储过程或扩展存储过程关键字
            strSQL = strSQL.Replace("xp_", "");
            strSQL = strSQL.Replace("sp_", "");

            //返回
            return strSQL;
        }
        #endregion
    }
}